from PyQt5.QtWidgets import (
    QApplication, QWidget, QVBoxLayout, QPushButton,
    QFileDialog, QTableWidget, QTableWidgetItem, QMessageBox, QLineEdit, QComboBox, QInputDialog
)
from PyQt5.QtWidgets import (
    QApplication,
    QLabel,
    QMainWindow,
    QPushButton,
    QVBoxLayout,
    QWidget,
    QButtonGroup,
    QHBoxLayout, QFileDialog, QMessageBox, QLineEdit, QComboBox, QMenuBar, QAction
)
from module.excel_trans import *
from module.utils import *
from module.log import *
from PyQt5.QtCore import Qt


class InsertWindow(QWidget):
    def __init__(self, signal_manager, db_path):
        super().__init__()
        self.db_path = db_path
        self.layout= QVBoxLayout()
        self.setWindowTitle('增')

        self.load_excel_button = QPushButton('选择Excel文件')
        self.load_excel_button.clicked.connect(self.load_excel)
        self.layout.addWidget(self.load_excel_button)

        self.table_name_label = QPushButton('请输入表名')  # 为了提示用户输入表名
        self.layout.addWidget(self.table_name_label)

        self.table_name_input = QLineEdit()  # 创建一个文本框，用户输入表名
        self.layout.addWidget(self.table_name_input)

        self.insert_db_button = QPushButton('插入')
        self.insert_db_button.clicked.connect(self.insert_to_db)
        self.layout.addWidget(self.insert_db_button)

        self.setLayout(self.layout)

        self.signal_manager = signal_manager

        self.df = None

    def load_excel(self):
        try:
            file_path, _ = QFileDialog.getOpenFileName(
                self, '选择Excel文件', '', 'Excel Files (*.xlsx *.xls)'
            )
            if file_path:
                df = pd.read_excel(file_path)
                self.df = df
                #释放信号，在table中显示出Excel
                self.signal_manager.emit_show_in_table_signal(df)

        except Exception as e:
            log_exception(e)
            raise

    def insert_to_db(self):
        table_name = self.table_name_input.text()
        if not table_name:
            QMessageBox.warning(self, '错误', '请输入表名！')
            return

        try:
            if self.df is not None:
                self.create_table(self.db_path, table_name)  # 创建表格
                self.insert_data_to_db(self.db_path, table_name)  # 插入数据
                QMessageBox.information(self, '成功', f'数据已成功插入到表 {table_name} 中。')
            else:
                QMessageBox.warning(self, '错误', '请先加载 Excel 文件！')
        except Exception as e:
            log_exception(e)
            QMessageBox.critical(self, '错误', f'插入数据时发生错误：{str(e)}')

    def create_table(self, db_path, table_name):
        """
        根据 DataFrame 创建 SQLite 表格
        :param db_path: SQLite 数据库路径
        :param table_name: 表名
        :return: None
        """
        columns = self.df.columns.tolist()  # 获取列名

        # 创建表的 SQL 语句
        create_sql = f"CREATE TABLE IF NOT EXISTS {table_name} ("
        create_sql += ", ".join([f"\"{col}\" TEXT" for col in columns])  # 默认所有列为 TEXT 类型
        create_sql += ");"

        try:
            conn = sqlite3.connect(db_path)
            cursor = conn.cursor()
            cursor.execute(create_sql)
            conn.commit()
            cursor.close()
            conn.close()
        except sqlite3.Error as e:
            log_exception(e)
            raise

    def insert_data_to_db(self, db_path, table_name):
        """
        将数据从 DataFrame 插入到 SQLite 表格
        :param db_path: SQLite 数据库路径
        :param table_name: 表名
        :return: None
        """
        columns = self.df.columns.tolist()
        placeholders = ','.join(['?'] * len(columns))
        insert_sql = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({placeholders})"

        try:
            conn = sqlite3.connect(db_path)
            cursor = conn.cursor()

            # 插入数据
            for index, row in self.df.iterrows():
                cursor.execute(insert_sql, tuple(row))

            conn.commit()


            cursor.close()
            conn.close()
        except sqlite3.Error as e:
            log_exception(e)
            raise Exception(f"插入数据到表 {table_name} 时发生错误")


class SearchWindow(QWidget):
    """
    现在只做了简单查询，选择表名直接返回表中所有的内容，功能更复杂的查询之后有需要可以扩展
    """
    def __init__(self, signal_manager, db_path):
        try:
            self.db_path = db_path
            super().__init__()
            self.setGeometry(100, 100, 300, 150)
            self.layout= QVBoxLayout()
            self.setWindowTitle('查')
            self.signal_manager = signal_manager

            self.sql_table_names = get_sqlite_names(db_path=self.db_path)

            self.tables_button = QPushButton('请选择要查询的表名:')
            self.layout.addWidget(self.tables_button)

            # 选项
            self.tables_combo = QComboBox()
            self.tables_combo.addItems(self.sql_table_names)
            self.layout.addWidget(self.tables_combo)

            # 执行查询语句
            self.execute_search_button = QPushButton("执行查询")
            self.execute_search_button.clicked.connect(self.execute_search)
            self.layout.addWidget(self.execute_search_button)

            self.setLayout(self.layout)

        except Exception as e:
            log_exception(e)
            raise

    def execute_search(self):
        searched_df = search_sql_to_df(self.db_path, table_name=self.tables_combo.currentText(), select_condition='*')
        self.signal_manager.emit_show_in_table_signal(searched_df)

class ProjectWindow(QWidget):
    def __init__(self, signal_manager, db_path):
        try:
            self.db_path = db_path
            super().__init__()
            self.setGeometry(200, 200, 600, 300)
            self.layout= QVBoxLayout()
            self.setWindowTitle('项目管理')
            self.signal_manager = signal_manager

            # 菜单栏
            self.menubar = QMenuBar(self)
            self.project_menu = self.menubar.addMenu("项目")

            self.import_action = QAction("导入项目", self)
            self.import_action.triggered.connect(self.import_project)

            self.export_action = QAction("导出项目", self)
            self.export_action.triggered.connect(self.export_project)

            self.project_menu.addAction(self.export_action)
            self.project_menu.addAction(self.import_action)

            self.layout.setMenuBar(self.menubar)

            # 复选框与输入框
            self.options = ['材料1','材料2','材料3','材料4','材料5', '材料6', '材料7', '材料8']
            self.combo_boxes = []
            self.line_edits = []

            self.selected_options = []

            self.create_options_selection()

            self.setLayout(self.layout)

        except Exception as e:
            log_exception(e)
            raise

    def create_options_selection(self):
        """动态创建材料选择框和对应的数量输入框"""
        for i, option in enumerate(self.options):
            if i >= 5 :
                break
            # 创建 QComboBox 并添加材料选项
            combo_box = QComboBox(self)
            combo_box.addItems(self.options)
            combo_box.setCurrentIndex(i)
            # 创建数量输入框
            line_edit = QLineEdit(self)
            self.combo_boxes.append(combo_box)
            self.line_edits.append(line_edit)

            # 创建布局来放置每个材料的选择框和输入框
            layout = QHBoxLayout()
            layout.addWidget(combo_box)
            layout.addWidget(QLabel("数量:"))
            layout.addWidget(line_edit)

            # 将布局添加到主布局中
            self.layout.addLayout(layout)

    def export_project(self):
        value, ok = QInputDialog.getText(self, '导出项目', '请输入项目名称', text='如：项目1')
        nums = [line_edit.text() for line_edit in self.line_edits]
        options = [combo_box.currentText() for combo_box in self.combo_boxes]
        txt_extract(options, nums, txt_path=f'./test/{value}.txt')
        QInputDialog(self,'提示','项目已成功导出！')

    def import_project(self):
        pass


class DeleteWindow(QWidget):
    def __init__(self, signal_manager, db_path):
        try:
            super().__init__()
            self.db_path = db_path
            self.signal_manager = signal_manager
            self.setWindowTitle('删')

            # 获取数据库表名
            self.sql_table_names = get_sqlite_names(db_path=self.db_path)

            # 创建布局
            layout = QVBoxLayout()

            # 表名选择器
            self.table_label = QLabel("请选择要删除数据的表：")
            layout.addWidget(self.table_label)
            self.tables_combo = QComboBox()
            self.tables_combo.addItems(self.sql_table_names)
            self.tables_combo.currentIndexChanged.connect(self.execute_search)
            layout.addWidget(self.tables_combo)

            # WHERE条件输入
            self.where_label = QLabel("WHERE条件（可选）：")
            layout.addWidget(self.where_label)
            self.where_condition = QLineEdit()
            self.where_condition.setPlaceholderText("例如：id = 1")
            layout.addWidget(self.where_condition)

            # 删除按钮
            self.delete_button = QPushButton("删除数据")
            self.delete_button.clicked.connect(self.delete_data)
            layout.addWidget(self.delete_button)

            self.setLayout(layout)

        except Exception as e:
            log_exception(e)
            raise

    def delete_data(self):
        table_name = self.tables_combo.currentText()
        where_clause = self.where_condition.text().strip()

        if not table_name:
            QMessageBox.warning(self, "错误", "请选择表名！")
            return

        # 确认操作
        confirm_msg = f"确定要删除表 '{table_name}' 的数据吗？"
        if where_clause:
            confirm_msg += f"\n条件：{where_clause}"
        else:
            confirm_msg += "\n（此操作将删除表中所有数据！）"

        confirm = QMessageBox.warning(
            self,
            "警告",
            confirm_msg,
            QMessageBox.Yes | QMessageBox.No,
            QMessageBox.No
        )
        if confirm != QMessageBox.Yes:
            return

        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            delete_sql = f"DELETE FROM {table_name}"
            if where_clause:
                delete_sql += f" WHERE {where_clause}"
            cursor.execute(delete_sql)
            conn.commit()
            QMessageBox.information(self, "成功", "数据删除成功！")
        except Exception as e:
            log_exception(e)
            QMessageBox.critical(self, "错误", f"删除失败：{str(e)}")
        finally:
            cursor.close()
            conn.close()

    def execute_search(self):
        searched_df = search_sql_to_df(self.db_path, table_name=self.tables_combo.currentText(), select_condition='*')
        self.signal_manager.emit_show_in_table_signal(searched_df)

class UpgradeWindow(QWidget):
    def __init__(self, signal_manager, db_path):
        try:
            super().__init__()
            self.db_path = db_path
            self.signal_manager = signal_manager
            self.setWindowTitle('改')

            # 主布局
            self.main_layout = QVBoxLayout()

            # 获取数据库表名
            self.sql_table_names = get_sqlite_names(db_path=self.db_path)

            # 表选择部分
            self.table_label = QLabel("选择要更新的表:")
            self.table_combo = QComboBox()
            self.table_combo.addItems(self.sql_table_names)
            self.table_combo.currentIndexChanged.connect(self.table_selected)
            self.main_layout.addWidget(self.table_label)
            self.main_layout.addWidget(self.table_combo)

            # 条件部分
            self.where_label = QLabel("WHERE条件 (例如: id=1):")
            self.where_input = QLineEdit()
            self.main_layout.addWidget(self.where_label)
            self.main_layout.addWidget(self.where_input)

            # 列和值部分 - 这里将动态生成
            self.columns_group = QWidget()
            self.columns_layout = QVBoxLayout()
            self.columns_group.setLayout(self.columns_layout)
            self.main_layout.addWidget(self.columns_group)

            # 按钮部分
            self.preview_button = QPushButton("预览要更新的记录")
            self.preview_button.clicked.connect(self.preview_records)
            self.main_layout.addWidget(self.preview_button)

            self.execute_button = QPushButton("执行更新")
            self.execute_button.clicked.connect(self.execute_update)
            self.main_layout.addWidget(self.execute_button)

            self.setLayout(self.main_layout)

            # 表格元数据缓存
            self.table_columns = []
            self.current_table = ""

            # 初始加载第一个表的列
            if self.sql_table_names:
                self.table_selected()

        except Exception as e:
            log_exception(e)
            raise

    def table_selected(self):
        """当选择表时，加载表的所有列，并创建对应的输入框"""
        table_name = self.table_combo.currentText()
        if table_name == self.current_table:
            return

        self.current_table = table_name

        # 清除旧的列输入框
        self.clear_column_inputs()

        # 获取表的列信息
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            cursor.execute(f"PRAGMA table_info({table_name})")
            columns = cursor.fetchall()
            self.table_columns = [column[1] for column in columns]

            # 为每列创建一个输入框
            self.column_widgets = []
            for column in self.table_columns:
                widget = QLineEdit()
                widget.setPlaceholderText(f"新 {column} 值 (留空则不更新)")
                self.columns_layout.addWidget(QLabel(f"{column}:"))
                self.columns_layout.addWidget(widget)
                self.column_widgets.append((column, widget))

            cursor.close()
            conn.close()

        except Exception as e:
            log_exception(e)
            QMessageBox.critical(self, "错误", f"获取表结构失败: {str(e)}")

    def preview_records(self):
        """预览将要更新的记录"""
        table_name = self.table_combo.currentText()
        where_condition = self.where_input.text().strip()

        if not table_name:
            QMessageBox.warning(self, "错误", "请选择表名!")
            return

        if not where_condition:
            QMessageBox.warning(self, "警告", "WHERE条件为空将更新所有记录!\n如需更新所有记录，请明确输入'1=1'")
            return

        try:
            # 构建查询语句
            query = f"SELECT * FROM {table_name} WHERE {where_condition}"
            df = search_sql_to_df(self.db_path, table_name=table_name, select_condition=f"WHERE {where_condition}")

            if df.empty:
                QMessageBox.information(self, "结果", "没有找到匹配的记录")
                return

            self.signal_manager.emit_show_in_table_signal(df)

        except Exception as e:
            log_exception(e)
            QMessageBox.critical(self, "错误", f"查询失败: {str(e)}")

    def execute_update(self):
        """执行更新操作"""
        table_name = self.table_combo.currentText()
        where_condition = self.where_input.text().strip()

        if not table_name:
            QMessageBox.warning(self, "错误", "请选择表名!")
            return

        if not where_condition:
            confirm = QMessageBox.warning(
                self,
                "警告",
                "WHERE条件为空将更新所有记录!\n确定要继续吗?",
                QMessageBox.Yes | QMessageBox.No
            )
            if confirm != QMessageBox.Yes:
                return

        # 收集要更新的列和值
        updates = []
        params = []
        for column, widget in self.column_widgets:
            value = widget.text().strip()
            if value:  # 只在值不为空时更新
                updates.append(f"{column} = ?")
                params.append(value)

        if not updates:
            QMessageBox.warning(self, "错误", "没有指定任何更新!")
            return

        # 构建更新语句
        update_sql = f"UPDATE {table_name} SET {', '.join(updates)} WHERE {where_condition}"

        try:
            confirm = QMessageBox.warning(
                self,
                "确认",
                f"确定要执行更新吗?\nSQL: {update_sql}",
                QMessageBox.Yes | QMessageBox.No
            )
            if confirm != QMessageBox.Yes:
                return

            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            cursor.execute(update_sql, params)
            conn.commit()
            affected_rows = cursor.rowcount
            cursor.close()
            conn.close()

            QMessageBox.information(
                self,
                "成功",
                f"更新成功! 影响的行数: {affected_rows}"
            )

            # 刷新显示更新后的数据
            self.preview_records()

        except Exception as e:
            log_exception(e)
            QMessageBox.critical(self, "错误", f"更新失败: {str(e)}")

    def clear_column_inputs(self):
        """清除所有列输入框"""
        # 移除所有列输入部件
        while self.columns_layout.count():
            child = self.columns_layout.takeAt(0)
            if child.widget():
                child.widget().deleteLater()

        self.table_columns = []
        self.column_widgets = []
